import os
import sqlite3
import pandas as pd
from module.log import log_exception

def excel_table(excel_path, db_path):
    excel_path = excel_path.replace("\\", "\\\\")
    db_path = db_path.replace("\\", "\\\\")
    table_name = excel_path.split("\\")[-1].split(".")[-2] # 表名

    excel_contene = pd.read_excel(excel_path)
    excel_list = [excel_contene.columns.tolist()] + excel_contene.values.tolist() # 二维列表存储Excel数据

    create_sql = f"create table \"{table_name}\"("
    for i in range(len(excel_list[0])):
        if i == 0:
            create_sql += f"\"{excel_list[0][i]}\" text primary key not null, "
        else:
            create_sql += f"\"{excel_list[0][i]}\" text not null, "
    create_sql = create_sql[:-2]
    create_sql += ");"
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute(create_sql) # 建表

    for i in range(1, len(excel_list)): # 插入数据
        insert_sql = f"insert into \"{table_name}\" values ("
        for j in range(len(excel_list[i])):
            insert_sql += f"\"{excel_list[i][j]}\", "
        insert_sql = insert_sql[:-2]
        insert_sql += f");"
        cursor.execute(insert_sql)

    conn.commit()
    cursor.close()
    conn.close()
    return


def create_table(df, db_path):
    table_name = df.columns[0]  # 假设表名是数据框的第一列
    create_sql = f"CREATE TABLE IF NOT EXISTS \"{table_name}\" ("

    # 创建表的 SQL
    for i in range(len(df.columns)):
        if i == 0:
            create_sql += f"\"{df.columns[i]}\" {df.iloc[0, i]} PRIMARY KEY NOT NULL, "
        else:
            create_sql += f"\"{df.columns[i]}\" {df.iloc[0, i]} NOT NULL, "

    create_sql = create_sql.rstrip(", ")  # 去掉最后的逗号
    create_sql += ");"

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute(create_sql)
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        log_exception(e)


def insert_to_db(df, db_path):
    table_name = df.columns[0]  # 假设表名是数据框的第一列
    insert_sql = f"INSERT INTO \"{table_name}\" VALUES ("

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        for i in range(1, len(df)):  # 从第二行数据开始插入
            insert_sql = f"INSERT INTO \"{table_name}\" VALUES ("
            for j in range(len(df.columns)):
                insert_sql += f"?, "
            insert_sql = insert_sql.rstrip(", ")  # 去掉最后的逗号
            insert_sql += ");"

            cursor.execute(insert_sql, tuple(df.iloc[i]))  # 使用元组插入数据

        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        log_exception(e)
